package com.gis.dao;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import com.gis.bean.Find;
import com.gis.bean.Find;
import com.gis.dto.FindDto;
import com.gis.dto.FindDto;
import com.gis.util.JDBCUtils;
import com.gis.util.PageResult;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

/**
 * @author t-zhaoheng
 * @since 2022/5/16
 */
public class FindDao {
  QueryRunner qr = new QueryRunner(JDBCUtils.getC3p0DateSource());
  private static final FindDao INSTANCE = new FindDao();

  private FindDao() {
  }

  public static FindDao getInstance() {
    return INSTANCE;
  }

  /**
   * 新增Find
   *
   * @param find
   * @return
   */
  public int add(Find find) {
    int i = 0;
    try {
      i = qr.execute(
          "insert into tb_find(username, goods_type_id, find_time, lng, lat, address, description, file_name) values(?,?,?,?,?,?,?,?)",
          find.getUsername(), find.getGoodsTypeId(), find.getFindTime(), find.getLng(),
          find.getLat(),
          find.getAddress(), find.getDescription(), find.getFileName());
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return i;
  }

  public List<FindDto> listAll(Map<String, Object> params) {
    List<FindDto> findDtos = new ArrayList<>();
    String sql = "select tb_find.id, username, lng, lat,address, description, find_time as findTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_find, tb_goods_type where tb_find.goods_type_id = tb_goods_type.id";
    // 参数值，即SQL中的问号对应的值
    List<Object> objList = new ArrayList<Object>();
    for (String key : params.keySet()) {
      if (key.equals("goodsTypeId") && params.get(key) != null) {
        sql += " and goods_type_id = ?";
        objList.add(params.get(key));
      } else if (key.equals("startDate") && params.get(key) != null) {
        sql += " and find_time > ?";
        objList.add(params.get(key));
      } else if (key.equals("endDate") && params.get(key) != null) {
        sql += " and find_time < ?";
        objList.add(params.get(key));
      } else if(key.equals("username") && params.get(key) != null) {
        if(!params.get(key).equals("admin")) {
          sql += " and username = ?";
          objList.add(params.get(key));
        }
      }
    }
    try {
      findDtos = qr.query(sql, ArrayUtil.toArray(objList, Object.class),new BeanListHandler<>(FindDto.class));
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return findDtos;
  }

  /**
   * 条件分页
   *
   * @param params
   * @return
   */
  @SuppressWarnings("deprecation")
  public PageResult<FindDto> page(Map<String, Object> params) {
    String sql = "select tb_find.id, username, lng, lat,address, description, find_time as findTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_find, tb_goods_type where tb_find.goods_type_id = tb_goods_type.id";
    String totalSql = "select count(1) from tb_find where 1=1";
    // 参数值，即SQL中的问号对应的值
    List<Object> objList = new ArrayList<Object>();
    for (String key : params.keySet()) {
      if (key.equals("goodsTypeId") && params.get(key) != null) {
        sql += " and goods_type_id = ?";
        totalSql += " and goods_type_id = ?";
        objList.add(params.get(key));
      } else if (key.equals("startDate") && params.get(key) != null) {
        sql += " and find_time > ?";
        totalSql += " and find_time > ?";
        objList.add(params.get(key));
      } else if (key.equals("endDate") && params.get(key) != null) {
        sql += " and find_time < ?";
        totalSql += " and find_time < ?";
        objList.add(params.get(key));
      } else if(key.equals("username") && params.get(key) != null) {
        if(!params.get(key).equals("admin")) {
          sql += " and username = ?";
          totalSql += " and username = ?";
          objList.add(params.get(key));
        }
      }
    }

    Long total = 0L;
    try {
      total = qr
          .query(totalSql, ArrayUtil.toArray(objList, Object.class), new ScalarHandler<>(1));
    } catch (SQLException e1) {
      // TODO Auto-generated catch block
      e1.printStackTrace();
    }

    objList.add(params.get("offset"));
    objList.add(params.get("rows"));
    sql += " order by find_time desc limit ?, ?";

    List<FindDto> findDtos = new ArrayList<>();

    try {
      findDtos = qr.query(sql, ArrayUtil.toArray(objList, Object.class),
          new BeanListHandler<>(FindDto.class));

    } catch (SQLException e) {
      e.printStackTrace();
    }
    PageResult<FindDto> pageResult = new PageResult<>();
    pageResult.setRows(findDtos);
    pageResult.setTotal(total);
    return pageResult;
  }

  /**
   * 分页查询 寻物启事
   *
   * @param pageSize
   * @param pageNum
   * @return
   */
  public List<FindDto> page(int pageSize, int pageNum) {
    List<FindDto> findDtos = new ArrayList<>();
    String sql = "select tb_find.id, username, lng, lat,address, description, find_time as findTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_find, tb_goods_type where tb_find.goods_type_id = tb_goods_type.id limit ?, ?";
    try {
      findDtos = qr
          .query(sql, new BeanListHandler<>(FindDto.class), (pageNum - 1) * pageSize,
              pageSize);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return findDtos;
  }

  /**
   * 查询总数
   *
   * @return
   */
  public Long total() {
    Long count = 0L;
    try {
      count = qr.query("select count(1) from tb_find", new ScalarHandler<>(1));
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return count;
  }

  /**
   * 获取失物信息的详细内容
   *
   * @param id
   * @return
   */
  public String getDetail(Integer id) {
    String sql = "select description from tb_find where id = ?";
    Map<String, Object> map = new HashMap<>();
    try {
      map = qr.query(sql, new MapHandler(), id);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return (String) map.get("description");
  }

  /**
   * delete find info by id
   *
   * @param id
   * @return
   */
  public int deleteFind(Integer id) {
    String sql = "delete from tb_find where id = ?";
    int i = 0;
    try {
      i = qr.update(sql, id);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return i;
  }

  /**
   * get find by id
   *
   * @param id
   * @return
   */
  public Find getFind(Integer id) {
    String sql = "select username, goods_type_id as goodsTypeId, find_time as findTime, lng, lat, address, description, file_name as fileName from tb_find where id = ?";
    Find find = null;
    try {
      find = qr.query(sql, new BeanHandler<>(Find.class), id);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return find;
  }

  /**
   * update find by id
   *
   * @param find
   * @return
   */
  public int updateFind(Find find) {

    String sql = "";
    int i = 0;
    try {
      if (StrUtil.isEmpty(find.getFileName())) {
        sql = "update tb_find set goods_type_id=?, find_time=?, lng=?, lat=?, address=?, description=? where id = ?";
        i = qr.update(sql, find.getGoodsTypeId(), find.getFindTime(), find.getLng(), find.getLat(),
            find.getAddress(), find.getDescription(), find.getId());
      } else {
        sql = "update tb_find set goods_type_id=?, find_time=?, lng=?, lat=?, address=?, description=?, file_name=? where id = ?";
        i = qr.update(sql, find.getGoodsTypeId(), find.getFindTime(), find.getLng(), find.getLat(),
            find.getAddress(), find.getDescription(), find.getFileName(), find.getId());
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return i;
  }
}
